# -*- coding: utf-8 -*-
'''
Created on Dec 15, 2013

@author: LONG HOANG GIANG
'''
import sys
import os
sys.path.append(os.path.join(os.path.dirname(__file__), '../'))
import lib
from database.sqlitehelper import SQLiteHelper
import sqlite3
import traceback

db = SQLiteHelper('/longhoanggiang/database/capcau.sqlite')
db2 = SQLiteHelper('/longhoanggiang/database/capcau-encrypted.sqlite')

def createTable(db):
    db.execute("CREATE TABLE IF NOT EXISTS capcau(id INTEGER PRIMARY KEY AUTOINCREMENT, en TEXT, vn TEXT, isActive INTEGER DEFAULT(1)); ")
    db.execute("CREATE TABLE IF NOT EXISTS capcau_fav(id INTEGER PRIMARY KEY AUTOINCREMENT, capcau_id INTEGER)")
    db.execute("CREATE INDEX IF NOT EXISTS idx_capcau_en_phrase ON capcau(en)")
    db.execute("CREATE INDEX IF NOT EXISTS idx_capcau_id ON capcau(id)")
    db.commit()
    

def add2DB(en, vi):
    if not isinstance(en, unicode): en = en.decode('utf-8')
    if not isinstance(vi, unicode): vi = vi.decode('utf-8')
    db.execute("INSERT INTO capcau(en, vn) VALUES(?, ?)", (en, vi))
    
def getAllWords():
    
    urlfm = 'http://tratu.vietgle.vn/hoc-tieng-anh/cap-cau-song-ngu/vietgle-tra-tu/tat-ca/trang-{0}.html'
    for page in range(1, 20249+1):
        url = urlfm.format(page)
        try:
            tree = lib.Web.load(url).build_tree()
            
    #         print lib.Etree.tostring(tree.xpath("//div[@id='ctl00_ContentPlaceHolderMain_ctl00']/ul"))
            
            for item in tree.xpath("//div[@id='ctl00_ContentPlaceHolderMain_ctl00']/ul"):
                
                phraseEn = lib.stringify(item.xpath("./li[1]"))
                phraseVi = lib.stringify(item.xpath("./li[2]"))
                
                print 'EN: ', phraseEn
                print 'VN: ', phraseVi
                print '------------------------------------------------------------------'
                add2DB(phraseEn, phraseVi)
        except:
            traceback.print_exc()
    db.commit()
    
def compress():
    compressDb = SQLiteHelper('/longhoanggiang/database/capcau-blob.sqlite')
    compressDb.execute("CREATE TABLE IF NOT EXISTS capcau(id INTEGER PRIMARY KEY AUTOINCREMENT, en BLOB, vn BLOB, isActive INTEGER DEFAULT(1)); ")
    compressDb.execute("CREATE TABLE IF NOT EXISTS capcau_fav(id INTEGER PRIMARY KEY AUTOINCREMENT, capcau_id INTEGER)")
    compressDb.execute("CREATE INDEX IF NOT EXISTS idx_capcau_id ON capcau(id)")
    compressDb.execute("CREATE INDEX IF NOT EXISTS idx_capcau_fav_id ON capcau_fav(capcau_id)")
    compressDb.commit()
    
    db.execute("SELECT en, vn FROM capcau")
    rows = db.fetchall()
    for row in rows:
        en = row[0]
        vn = row[1]
        print en, vn
        en = sqlite3.Binary(lib.compressStr(lib.encryptCipher(en)))
        vn = sqlite3.Binary(lib.compressStr(lib.encryptCipher(vn)))
        compressDb.execute("INSERT INTO capcau(en, vn) VALUES(?, ?)", (en, vn))
    compressDb.commit()
    compressDb.close()
    
        
def test():
    adb =  SQLiteHelper('/longhoanggiang/database/capcau-blob.sqlite')
    adb.execute("SELECT en, vn FROM capcau")
    rows = adb.fetchone()
    
    en = rows[0]
    vn = rows[1]
    print lib.decryptCipher(lib.decompressStr(en))
    print lib.decryptCipher(lib.decompressStr(vn))
    
    adb.close()   
    
    
# def copy2Encrypt():
#     db.execute("SELECT en, vn, isActive FROM capcau")
#     rows = db.fetchall()
#     for row in rows:
#         phraseEn = row[0]
#         phraseVn = row[1]
#         isActive = row[2]
#         phraseEnEncrypt = lib.encryptCipher(phraseEn)
#         print phraseEn
#         print phraseEnEncrypt
#         print phraseVn
#         
#         db2.execute("INSERT INTO capcau(en, vn) VALUES(?, ?)", (phraseEnEncrypt, phraseVn))
#     db2.commit()
#     db2.close()
            
if __name__ == '__main__':
    
#     createTable(db2)
#     createTable(db)
#     getAllWords()
#     copy2Encrypt()
    compress()
#     test()    
    db.close()
    print '> Finished'
    sys.exit(1)
    